// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System.Collections;
using System.Data.Common;

namespace net.windward.utils.ado.Oracle
{
	///<summary>
	/// A lightweight handler of functionality that the ODBC and OleDb drivers can use as well as the main implementations.
	///</summary>
	public class WrOracleSyntax : IWrSyntax
	{

		private readonly DbProviderFactory provider;
        private static readonly Hashtable reservedWords = new Hashtable();

        static WrOracleSyntax()
        {
            foreach (string str in _reservedWords)
                reservedWords.Add(str, str);
        }

		/// <summary>
		/// Create the object.
		/// </summary>
		/// <param name="provider">The provider for this connector.</param>
		public WrOracleSyntax(DbProviderFactory provider)
		{
			this.provider = provider;
		}

		/// <summary>
		/// Convert a select to normalize the "top 10" syntax
		/// </summary>
		/// <param name="select">The select with our standard usage</param>
		/// <returns>The select for this database.</returns>
		public string ConvertSelect(string select)
		{
			int pos = select.ToLower().LastIndexOf(" top ");
			if (pos == -1)
				return select;

			// {select} + {where rownum <= N} + {order by ...}
			string num = "ROWNUM <= " + select.Substring(pos + 5).Trim();
			select = select.Substring(0, pos).Trim();
			return "select * from (" + select + ") where " + num;
		}

		/// <summary>
		/// Create a safe table name. Generally needed only if the name has a space in it or is a reserved name.
		/// </summary>
		/// <param name="tableName">The true table name.</param>
		/// <returns>The table name as it should be used in a select.</returns>
		public string SafeTableName(string tableName)
		{
            return MakeNameSafe(tableName);
		}

		/// <summary>
		/// Create a safe column name. Generally needed only if the name has a space in it or is a reserved name.
		/// </summary>
		/// <param name="columnName">The true column name.</param>
		/// <returns>The table name as it should be used in a select.</returns>
		public string SafeColumnName(string columnName)
		{
            return MakeNameSafe(columnName);
		}

		/// <summary>
		/// Create a safe table name. Generally needed only if the name has a space in it or is a reserved name.
		/// </summary>
		/// <param name="tableName">The true table name.</param>
		/// <param name="columnName">The true column name.</param>
		/// <returns>The table.column name as it should be used in a select.</returns>
		public string SafeTableColumnName(string tableName, string columnName)
		{
			return SafeTableName(tableName) + '.' + SafeColumnName(columnName);
		}

        private static string MakeNameSafe(string columnName)
        {
            if (columnName[0] == '"' && columnName[columnName.Length - 1] == '"')
                return columnName;
            if (columnName.IndexOf(' ') != -1)
            {
                if (columnName[0] == '"')
                    return columnName + "\"";
                if (columnName[columnName.Length - 1] == '"')
                    return "\"" + columnName;
                return "\"" + columnName + "\"";
            }
            if (reservedWords.ContainsKey(columnName.ToUpper()))
            {
                if (columnName[0] == '"')
                    return columnName + "\"";
                if (columnName[columnName.Length - 1] == '"')
                    return "\"" + columnName;
                return "\"" + columnName + "\"";
            }
            return columnName;
        }

        private static readonly string[] _reservedWords = {
		                                                 	"ADD", "ALL", "ALTER", "ANALYZE", "AND", "AS", "ASC", "ASENSITIVE",
		                                                 	"BEFORE", "BETWEEN", "BIGINT", "BINARY", "BLOB", "BOTH", "BY",
		                                                 	"CALL", "CASCADE", "CASE", "CHANGE", "CHAR", "CHARACTER", "CHECK",
		                                                 	"COLLATE", "COLUMN", "CONDITION", "CONSTRAINT", "CONTINUE",
		                                                 	"CONVERT", "CREATE", "CROSS", "CURRENT_DATE", "CURRENT_TIME",
		                                                 	"CURRENT_TIMESTAMP", "CURRENT_USER", "CURSOR", "DATABASE",
		                                                 	"DATABASES", "DAY_HOUR", "DAY_MICROSECOND", "DAY_MINUTE",
		                                                 	"DAY_SECOND", "DEC", "DECIMAL", "DECLARE", "DEFAULT", "DELAYED",
		                                                 	"DELETE", "DESC", "DESCRIBE", "DETERMINISTIC", "DISTINCT",
		                                                 	"DISTINCTROW", "DIV", "DOUBLE", "DROP", "DUAL", "EACH", "ELSE",
		                                                 	"ELSEIF", "ENCLOSED", "ESCAPED", "EXISTS", "EXIT", "EXPLAIN",
		                                                 	"FALSE", "FETCH", "FLOAT", "FLOAT4", "FLOAT8", "FOR", "FORCE",
		                                                 	"FOREIGN", "FROM", "FULLTEXT", "GRANT", "GROUP", "HAVING",
		                                                 	"HIGH_PRIORITY", "HOUR_MICROSECOND", "HOUR_MINUTE", "HOUR_SECOND",
		                                                 	"IF", "IGNORE", "IN", "INDEX", "INFILE", "INNER", "INOUT",
		                                                 	"INSENSITIVE", "INSERT", "INT", "INT1", "INT2", "INT3", "INT4",
		                                                 	"INT8", "INTEGER", "INTERVAL", "INTO", "IS", "ITERATE", "JOIN",
		                                                 	"KEY", "KEYS", "KILL", "LEADING", "LEAVE", "LEFT", "LIKE", "LIMIT",
		                                                 	"LINES", "LOAD", "LOCALTIME", "LOCALTIMESTAMP", "LOCK", "LONG",
		                                                 	"LONGBLOB", "LONGTEXT", "LOOP", "LOW_PRIORITY", "MATCH",
		                                                 	"MEDIUMBLOB", "MEDIUMINT", "MEDIUMTEXT", "MIDDLEINT",
		                                                 	"MINUTE_MICROSECOND", "MINUTE_SECOND", "MOD", "MODIFIES", "NATURAL",
		                                                 	"NOT", "NO_WRITE_TO_BINLOG", "NULL", "NUMERIC", "ON", "OPTIMIZE",
		                                                 	"OPTION", "OPTIONALLY", "OR", "ORDER", "OUT", "OUTER", "OUTFILE",
		                                                 	"PRECISION", "PRIMARY", "PROCEDURE", "PURGE", "READ", "READS",
		                                                 	"REAL", "REFERENCES", "REGEXP", "RELEASE", "RENAME", "REPEAT",
		                                                 	"REPLACE", "REQUIRE", "RESTRICT", "RETURN", "REVOKE", "RIGHT",
		                                                 	"RLIKE", "SCHEMA", "SCHEMAS", "SECOND_MICROSECOND", "SELECT",
		                                                 	"SENSITIVE", "SEPARATOR", "SET", "SHOW", "SMALLINT", "SONAME",
		                                                 	"SPATIAL", "SPECIFIC", "SQL", "SQLEXCEPTION", "SQLSTATE",
		                                                 	"SQLWARNING", "SQL_BIG_RESULT", "SQL_CALC_FOUND_ROWS",
		                                                 	"SQL_SMALL_RESULT", "SSL", "STARTING", "STRAIGHT_JOIN", "TABLE",
		                                                 	"TERMINATED", "THEN", "TINYBLOB", "TINYINT", "TINYTEXT", "TO",
		                                                 	"TRAILING", "TRIGGER", "TRUE", "UNDO", "UNION", "UNIQUE", "UNLOCK",
		                                                 	"UNSIGNED", "UPDATE", "USAGE", "USE", "USING", "UTC_DATE",
		                                                 	"UTC_TIME", "UTC_TIMESTAMP", "VALUES", "VARBINARY", "VARCHAR",
		                                                 	"VARCHARACTER", "VARYING", "WHEN", "WHERE", "WHILE", "WITH", "WRITE"
		                                                 	, "XOR", "YEAR_MONTH", "ZEROFILL"
		                                                 };
	}
}
